/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cashmate.struts;

import com.cashmate.hibernate.pojo.Dccr;
import com.cashmate.hibernate.pojo.Employees;
import com.cashmate.hibernate.pojo.Summary;
import com.cashmate.utilities.DCCRDate;
import com.cashmate.utilities.PerformanceObject;
import com.cashmate.utilities.Utils;
import com.opensymphony.xwork2.ActionSupport;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Result;
import org.joda.time.DateTime;

/**
 *
 * @author clione
 */
public class ExportExcel extends ActionSupport {

    Logger logger = Logger.getLogger(ExportExcel.class);
    private String type = "";
    private final Employees emps = (Employees) ServletActionContext.getRequest().getSession().getAttribute("employee");
    private FileInputStream inputStream;
    private FileOutputStream outputStream;
    private String filename;
    private File tempDir = (File) ServletActionContext.getServletContext().getAttribute("javax.servlet.context.tempdir");
    private final SimpleDateFormat sdf = new SimpleDateFormat("ddMMMyy-hhmmssa");

    @Action(value = "/export-excel", results = {
        @Result(name = "success", type = "stream", params = {"inputName", "inputStream", "contentType", "application/ms-excel", "contentDisposition", "attachment; filename=${filename}"})
    })
    @Override
    public String execute() throws Exception {
        try {
            if (!type.isEmpty()) {
                String date = sdf.format(new Date());
                if (type.equals("dccr")) {
                    List<Dccr> dccrList = (List<Dccr>) ServletActionContext.getRequest().getSession().getAttribute("dccr");
                    if (dccrList != null && dccrList.size() > 0) {
                        if (!emps.getBranches().getBranchcode().equals("HO")) {
                            filename = emps.getBranches().getBranchcode().toLowerCase() + "DCCR" + date.replaceAll(",", "").replaceAll(" ", "_").replaceAll(":", "-") + ".xls";
                        } else {
                            filename = "DCCR_" + date + ".xls";
                        }
                        //logger.debug(tempDir.getPath() + "\\" + filename + ".xls");
                        File dccrExcel = new File(tempDir.getPath() + "\\" + filename);
                        outputStream = new FileOutputStream(dccrExcel);

                        HSSFWorkbook wb = new HSSFWorkbook();
                        CreationHelper ch = wb.getCreationHelper();
                        String[] headers = {"Date Generated", "Area", "Borrower ID", "Borrower Code", "Name", "Loan No.", "Date Release", "Maturity Date", "Days Left", "Daily Amort", "1st Day", "2nd Day", "3rd Day", "4th Day", "5th Day", "6th Day", "7th Day", "Debited Payment",
                            "Tot Cut-Off Payment", "Tot Cut-Off Collection", "Cut-Off Due", "Today's Due", "Overdue", "Total Advance", "Due+OD", "Total Payment", "Balance",
                            "PN", "Current PN", "Last Cut-Off"};
                        Sheet s = null;
                        if (!emps.getBranches().getBranchcode().equals("HO")) {
                            s = createReportSheet(wb, emps.getBranches().getBranchcode() + " Daily Cash Collection Report", date, emps.getCompletename(), headers, 29);
                        } else {
                            s = createReportSheet(wb, "Daily Cash Collection Report", date, emps.getCompletename() + " (HO)", headers, 29);
                        }
                        int i = 5;
                        for (Dccr d : dccrList) {
                            Row row = s.createRow(++i);
                            //logger.debug(row.getRowNum());
                            row.createCell(0).setCellValue(ch.createRichTextString(d.getDategenerated()));
                            row.createCell(1).setCellValue(ch.createRichTextString(d.getAreacode()));
                            row.createCell(2).setCellValue(d.getBorrowerid());
                            row.createCell(3).setCellValue(ch.createRichTextString(d.getBorrowercode()));
                            row.createCell(4).setCellValue(ch.createRichTextString(d.getBorrowername()));
                            row.createCell(5).setCellValue(d.getNumloan());
                            row.createCell(6).setCellValue(ch.createRichTextString(d.getReleasedate()));
                            row.createCell(7).setCellValue(ch.createRichTextString(d.getMaturitydate()));
                            row.createCell(8).setCellValue(d.getDaysleft());
                            row.createCell(9).setCellValue(d.getDailyamort());
                            row.createCell(10).setCellValue(d.getP1());
                            row.createCell(11).setCellValue(d.getP2());
                            row.createCell(12).setCellValue(d.getP3());
                            row.createCell(13).setCellValue(d.getP4());
                            row.createCell(14).setCellValue(d.getP5());
                            row.createCell(15).setCellValue(d.getP6());
                            row.createCell(16).setCellValue(d.getP7());
                            row.createCell(17).setCellValue(d.getDebitedpayment());
                            row.createCell(18).setCellValue(d.getTotalcutoffpayment());
                            row.createCell(19).setCellValue(d.getTotalcutoffcollection());
                            row.createCell(20).setCellValue(d.getCutoffdue());
                            row.createCell(21).setCellValue(d.getTodaysdue());
                            row.createCell(22).setCellValue(d.getOverdue());
                            row.createCell(23).setCellValue(d.getTotaladvance());
                            row.createCell(24).setCellValue(d.getDueplusod());
                            row.createCell(25).setCellValue(d.getTotalpayment());
                            row.createCell(26).setCellValue(d.getBalance());
                            row.createCell(27).setCellValue(d.getPromisorynote());
                            row.createCell(28).setCellValue(d.getCurrentpn());
                            row.createCell(29).setCellValue(d.getLastcutoff());
                        }
                        if (dccrExcel.canWrite()) {
                            s.setColumnWidth(0, 5000);
                            s.autoSizeColumn(14);
                            s.autoSizeColumn(1);
                            s.autoSizeColumn(15);
                            s.autoSizeColumn(2);
                            s.autoSizeColumn(16);
                            s.autoSizeColumn(3);
                            s.autoSizeColumn(17);
                            s.autoSizeColumn(4);
                            s.autoSizeColumn(18);
                            s.autoSizeColumn(5);
                            s.autoSizeColumn(19);
                            s.autoSizeColumn(6);
                            s.autoSizeColumn(20);
                            s.autoSizeColumn(7);
                            s.autoSizeColumn(21);
                            s.autoSizeColumn(8);
                            s.autoSizeColumn(22);
                            s.autoSizeColumn(9);
                            s.autoSizeColumn(23);
                            s.autoSizeColumn(10);
                            s.autoSizeColumn(24);
                            s.autoSizeColumn(11);
                            s.autoSizeColumn(25);
                            s.autoSizeColumn(12);
                            s.autoSizeColumn(26);
                            s.autoSizeColumn(13);
                            s.autoSizeColumn(27);
                            s.autoSizeColumn(28);
                            s.autoSizeColumn(29);
                            s.protectSheet("dccrpassword");
                            wb.write(outputStream);
                            outputStream.close();
                        } else {
                            throw new IOException("Cannot write to file: " + dccrExcel.getAbsolutePath());
                        }
                        if (dccrExcel.exists() && dccrExcel.canRead()) {
                            inputStream = new FileInputStream(dccrExcel);
                        } else {
                            throw new IOException("Cannot read file: " + dccrExcel.getAbsolutePath());
                        }
                    }
                } else if (type.equals("week")) {
                    List<Summary> weekList = (List<Summary>) ServletActionContext.getRequest().getSession().getAttribute("weekperf");
                    String weekdateString = ServletActionContext.getRequest().getSession().getAttribute("dateweekperf").toString();
                    DCCRDate weekdate = new DCCRDate(Utils.stringArrToDateTime(weekdateString.split("/")));
                    if (weekList != null && weekList.size() > 0 && !weekdateString.isEmpty()) {
                        String branch = Utils.segregate("letters", weekList.get(0).getAreacode()).toLowerCase();
                        DateTime f = Utils.stringArrToDateTime(weekdate.getPaymentday1().split("/"));
                        DateTime e = Utils.stringArrToDateTime(weekdate.getCutoff().split("/"));
                        filename = branch + "Weekly(" + f.getDayOfMonth() + "-" + e.getDayOfMonth() + ")_" + date + ".xls";

                        File summaryExcel = new File(tempDir.getPath() + "\\" + filename);
                        outputStream = new FileOutputStream(summaryExcel);

                        HSSFWorkbook wb = new HSSFWorkbook();
                        CreationHelper ch = wb.getCreationHelper();
                        String[] headers = {"Area", "Rating", "Cut-off Due", "Overdue", "Due+Od", "Advance", "Debited Payment", "Total Payment", "Total Collection", "PN Generated", "New Accounts", "Accounts Released", "PN N/A"};
                        Sheet s = null;
                        if (!emps.getBranches().getBranchcode().equals("HO")) {
                            s = createReportSheet(wb, branch.toUpperCase() + " Weekly Performance Rating (" + weekdate.getPaymentday1() + "-" + weekdate.getCutoff() + ")", date, emps.getCompletename(), headers, 12);
                        } else {
                            s = createReportSheet(wb, branch.toUpperCase() + " Weekly Performance Rating (" + weekdate.getPaymentday1() + "-" + weekdate.getCutoff() + ")", date, emps.getCompletename() + " (HO)", headers, 12);
                        }
                        int i = 6;
                        for (Summary ss : weekList) {
                            Row row = s.createRow(++i);
                            row.createCell(0).setCellValue(ch.createRichTextString(ss.getAreacode()));
                            row.createCell(1).setCellValue(ch.createRichTextString(ss.getRating()));
                            row.createCell(2).setCellValue(ss.getCutoffdue());
                            row.createCell(3).setCellValue(ss.getOverdue());
                            row.createCell(4).setCellValue(ss.getDueplusod());
                            row.createCell(5).setCellValue(ss.getTotaladvance());
                            row.createCell(6).setCellValue(ss.getDebitedpayment());
                            row.createCell(7).setCellValue(ss.getComputedtotalpayment());
                            row.createCell(8).setCellValue(ss.getTotalcutoffcollection());
                            row.createCell(9).setCellValue(ss.getPngenerated());
                            row.createCell(10).setCellValue(ss.getNewacct());
                            row.createCell(11).setCellValue(ss.getTotacctrel());
                            row.createCell(12).setCellValue(ss.getPnna());
                        }
                        if (summaryExcel.canWrite()) {
                            s.setColumnWidth(0, 5000);
                            s.autoSizeColumn(7, true);
                            s.autoSizeColumn(8, true);
                            s.autoSizeColumn(9, true);
                            s.autoSizeColumn(10, true);
                            s.autoSizeColumn(11, true);
                            s.autoSizeColumn(12, true);
                            s.autoSizeColumn(1, true);
                            s.autoSizeColumn(2, true);
                            s.autoSizeColumn(3, true);
                            s.autoSizeColumn(4, true);
                            s.autoSizeColumn(5, true);
                            s.autoSizeColumn(6, true);

                            s.protectSheet("summarypassword");
                            wb.write(outputStream);
                            outputStream.close();
                        } else {
                            throw new IOException("Cannot write to file: " + summaryExcel.getAbsolutePath());
                        }
                        if (summaryExcel.exists() && summaryExcel.canRead()) {
                            inputStream = new FileInputStream(summaryExcel);
                        } else {
                            throw new IOException("Cannot read file: " + summaryExcel.getAbsolutePath());
                        }
                    }
                } else if (type.equals("month")) {
                    HashMap<String, List<PerformanceObject>> monthExportMap = (HashMap<String, List<PerformanceObject>>) ServletActionContext.getRequest().getSession().getAttribute("monthexport");
                    String[] monthdet = ServletActionContext.getRequest().getSession().getAttribute("monthdetails").toString().split("-");
                    if (monthExportMap != null && monthExportMap.size() > 0 && monthdet.length == 3) {
                        filename = monthdet[2].toLowerCase() + "Monthly(" + monthdet[0] + "-" + monthdet[1] + ")_" + date + ".xls";

                        File monthExcel = new File(tempDir.getPath() + "\\" + filename);
                        outputStream = new FileOutputStream(monthExcel);
                        DateTime dt = new DateTime(Integer.parseInt(monthdet[1]), Integer.parseInt(monthdet[0]), 1, 12, 0, 0, 0);
                        HSSFWorkbook wb = new HSSFWorkbook();
                        CreationHelper ch = wb.getCreationHelper();
                        String m = dt.monthOfYear().getAsShortText();
                        String[] headers = {"Area", m + " 01-06",
                            m + " 07-12",
                            m + " 13-18",
                            m + " 19-24",
                            m + " 25-" + dt.dayOfMonth().getMaximumValue(),
                            "Total"};

                        for (String ss : monthExportMap.keySet()) {
                            Sheet s = null;
                            List<PerformanceObject> perfList = monthExportMap.get(ss);
                            if (perfList != null && perfList.size() > 0) {
                                if (ss.equals("DUE")) {
                                    if (!emps.getBranches().getBranchcode().equals("HO")) {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Cut-off Due Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename(), headers, 7);
                                    } else {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Cut-off Due Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename() + " (HO)", headers, 7);
                                    }
                                } else if (ss.equals("TCC")) {
                                    if (!emps.getBranches().getBranchcode().equals("HO")) {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Total Cut-off Collection Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename(), headers, 7);
                                    } else {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Total Cut-off Collection Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename() + " (HO)", headers, 7);
                                    }
                                } else if (ss.equals("TP")) {
                                    if (!emps.getBranches().getBranchcode().equals("HO")) {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Total Payment Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename(), headers, 7);
                                    } else {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Total Payment Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename() + " (HO)", headers, 7);
                                    }
                                } else if (ss.equals("DPO")) {
                                    if (!emps.getBranches().getBranchcode().equals("HO")) {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Due + OD Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename(), headers, 7);
                                    } else {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Due + OD Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename() + " (HO)", headers, 7);
                                    }
                                } else if (ss.equals("OD")) {
                                    if (!emps.getBranches().getBranchcode().equals("HO")) {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Overdue Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename(), headers, 7);
                                    } else {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Overdue Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename() + " (HO)", headers, 7);
                                    }
                                } else if (ss.equals("RTNG")) {
                                    if (!emps.getBranches().getBranchcode().equals("HO")) {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Performance Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename(), headers, 7);
                                    } else {
                                        s = createReportSheet(wb, monthdet[2].toUpperCase() + " Performance Rating (" + m + " " + monthdet[1] + ")", date, emps.getCompletename() + " (HO)", headers, 7);
                                    }
                                }
                                int i = 5;
                                for (PerformanceObject po : perfList) {
                                    Row row = s.createRow(++i);
                                    row.createCell(0).setCellValue(ch.createRichTextString(po.getAreacode()));
                                    row.createCell(1).setCellValue(po.getFirstcut());
                                    row.createCell(2).setCellValue(po.getSecondcut());
                                    row.createCell(3).setCellValue(po.getThirdcut());
                                    row.createCell(4).setCellValue(po.getFourthcut());
                                    row.createCell(5).setCellValue(po.getFifthcut());
                                    if (!ss.equals("RTNG")) {
                                        row.createCell(6).setCellValue(po.getTotalperarea());
                                    } else {
                                        row.createCell(6).setCellValue(po.getTotalrate());
                                    }
                                }
                                s.setColumnWidth(0, 5000);
                                s.autoSizeColumn(1);
                                s.autoSizeColumn(2);
                                s.autoSizeColumn(3);
                                s.autoSizeColumn(4);
                                s.autoSizeColumn(5);
                                s.autoSizeColumn(6);
                                s.protectSheet("monthpassword");

                            }
                        }
                        if (monthExcel.canWrite()) {
                            wb.write(outputStream);
                            outputStream.close();
                        } else {
                            throw new IOException("Cannot write to file: " + monthExcel.getAbsolutePath());
                        }
                        if (monthExcel.exists() && monthExcel.canRead()) {
                            inputStream = new FileInputStream(monthExcel);
                        } else {
                            throw new IOException("Cannot read file: " + monthExcel.getAbsolutePath());
                        }
                    }
                }
            }

        } catch (Exception e) {
            if (emps != null) {
                logger.fatal("(" + emps.getIdnumber() + "):" + e.getLocalizedMessage(), e);
            } else {
                logger.fatal(e.getLocalizedMessage(), e);
            }
            e.printStackTrace();
            addActionError(e.getLocalizedMessage());
            return ERROR;
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }

        return SUCCESS;
    }

    public void setType(String type) {
        this.type = type;
    }

    public InputStream getInputStream() {
        return inputStream;
    }

    public String getFilename() {
        return filename;
    }

    private Sheet createReportSheet(HSSFWorkbook wb, String reportName, String date, String auth, String[] headers, int headmerge) throws Exception {
        Sheet s = wb.createSheet(reportName);
        CreationHelper ch = wb.getCreationHelper();
        Row h1 = s.createRow((short) 0);
        createCell(wb, h1, (short) 2, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, (short) 24, true, 1).setCellValue(ch.createRichTextString("Cashmate Credit Corporation"));
        s.addMergedRegion(new CellRangeAddress(0, 0, 2, headmerge));

        Row h2 = s.createRow((short) 1);
        createCell(wb, h2, (short) 2, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, (short) 18, true, 2).setCellValue(ch.createRichTextString(reportName));
        s.addMergedRegion(new CellRangeAddress(1, 1, 2, headmerge));

        Row h3 = s.createRow((short) 2);
        createCell(wb, h3, (short) 2, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, (short) 14, true, 2).setCellValue(ch.createRichTextString("Generation Date: " + date));
        s.addMergedRegion(new CellRangeAddress(2, 2, 2, headmerge));

        Row h4 = s.createRow((short) 3);
        createCell(wb, h4, (short) 2, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, (short) 14, true, 2).setCellValue(ch.createRichTextString("Generated By: " + auth));
        s.addMergedRegion(new CellRangeAddress(3, 3, 2, headmerge));

        Row space = s.createRow((short) 4);
        createCell(wb, space, (short) 2, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, (short) 14, true, 2).setCellValue(ch.createRichTextString(" "));
        s.addMergedRegion(new CellRangeAddress(4, 4, 2, headmerge));

        Row h5 = s.createRow((short) 5);

        if (reportName.contains("Weekly Performance Rating")) {
            Row h6 = s.createRow((short) 6);
            for (int i = 0; i < headers.length; i++) {
                if (i == 4 || i == 5) {
                    if (i == 4) {
                        createCell(wb, h5, (short) i, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_CENTER, (short) 12, true, 3).setCellValue(ch.createRichTextString("Payment For"));
                        s.addMergedRegion(new CellRangeAddress(5, 5, 4, 5));
                        createCell(wb, h6, (short) i, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_CENTER, (short) 12, true, 3).setCellValue(ch.createRichTextString(headers[i]));
                    } else if (i == 5) {
                        createCell(wb, h6, (short) i, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_CENTER, (short) 12, true, 3).setCellValue(ch.createRichTextString(headers[i]));
                    }
                } else {
                    createCell(wb, h5, (short) i, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_CENTER, (short) 12, true, 3).setCellValue(ch.createRichTextString(headers[i]));
                    s.addMergedRegion(new CellRangeAddress(5, 6, i, i));
                }
            }
            s.createFreezePane(0, 7, 0, 7);
        } else {
            for (int i = 0; i < headers.length; i++) {
                createCell(wb, h5, (short) i, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_CENTER, (short) 12, true, 3).setCellValue(ch.createRichTextString(headers[i]));
            }
            s.createFreezePane(0, 6, 0, 6);
        }
        s.addMergedRegion(new CellRangeAddress(0, 4, 0, 1));
        InputStream is = new FileInputStream(new File(ServletActionContext.getServletContext().getRealPath("/res/sandbox/CashmateLoGo.png")));
        byte[] bytes = IOUtils.toByteArray(is);
        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        is.close();
        CreationHelper helper = wb.getCreationHelper();
        Drawing drawing = s.createDrawingPatriarch();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(0);
        anchor.setRow1(0);
        anchor.setDx1(650);
        anchor.setDy1(300);
        Picture pic = drawing.createPicture(anchor, pictureIdx);
        pic.resize();

        return s;
    }

    private Cell createCell(HSSFWorkbook wb, Row row, short column, short halign, short valign, short fontsize, boolean bold, int type) {
        Cell cell = row.createCell(column);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        Font font = wb.createFont();
        font.setFontHeightInPoints(fontsize);
        if (bold) {
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        }
        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short) 12, (byte) 0, (byte) 176, (byte) 80);
        switch (type) {
            case 1:
                font.setColor(HSSFColor.BLACK.index);
                cellStyle.setFillForegroundColor(palette.getColor((short) 12).getIndex());
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                break;
            case 2:
                font.setColor(HSSFColor.WHITE.index);
                cellStyle.setFillForegroundColor(palette.getColor((short) 12).getIndex());
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                break;
            default:
                font.setColor(HSSFColor.BLACK.index);
                break;
        }
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        return cell;
    }
}
